﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_BCDTBSX : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoDoanhThu";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttban = 0, ttcvc = 0;
    static DataTable dt;
    static DataTable dtPrint;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoaiBienSo(dlBienSo);
            LoadTramTron(dlTramTron);
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoaiBienSo(DropDownList dlBienSo)
    {
        dlBienSo.Items.Clear();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;
    }
    void LoadBSX(DropDownList dlBienSo)
    {
        dlBienSo.Items.Clear();
        dlBienSo.DataSource = ex.GetData("sp_BCDT_BSX_LoadBienSoXe");
        dlBienSo.DataBind();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(CurrentPage);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    void Search(int page)
    {
        int index = dlBienSo.SelectedIndex > 0 ? 1 : 2;


        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        p[3] = new SqlParameter("@Index", index);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@PageNumber", page);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@RowspPage", PageNumber);
        p[5].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCDT_BSX", p);
        GV.DataBind();

        //get footer
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        p[3] = new SqlParameter("@Index", index);
        p[3].SqlDbType = SqlDbType.Int;

        if (GV.Rows.Count > 0)
        {
            dt = ex.GetData("sp_BCDT_BSX_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                klban = double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                ttcvc = double.Parse(dt.Rows[0]["TTCVC"].ToString());
                ttban = double.Parse(dt.Rows[0]["TTGiaHD"].ToString());

                GV.FooterRow.Cells[6].Text = "Tổng cộng";
                GV.FooterRow.Cells[6].HorizontalAlign = HorizontalAlign.Center;
                GV.FooterRow.Cells[9].Text = ex.ConvertDecimal(kltx);
                GV.FooterRow.Cells[11].Text = ex.ConvertDecimal(klban);
                GV.FooterRow.Cells[15].Text = ex.ConvertDecimal(ttban);
                GV.FooterRow.Cells[17].Text = ex.ConvertDecimal(ttcvc);
            }
        }
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlBienSo.SelectedIndex > 0)
                sheetname = dlBienSo.SelectedItem.Text;

            else sheetname = "Bảng tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO BIỂN SỐ XE " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            if (dlBienSo.SelectedIndex > 0)
            {
                //bien so
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue(dlBienSo.SelectedIndex > 0 ? "Biển số: " + dlBienSo.SelectedItem.Text : "");
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;
            }
            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion


            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Số phiếu", "Biển số", "Lái xe", "Giờ xuất trạm", "Khách hàng", "Độ sụt", "Mác TX", "KL thực xuất", "Mác HĐ", "KL bán", "Loại đá", "Giá hợp đồng", "TT bê tông", "Cước VC", "Thành tiền CVC" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count - 1; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 9 || j == 11 || j >= 13)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 2 || j == 5 || j == 7 || j == 8 || j == 10)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }

            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(6);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "J", "K", "L", "M", "N", "O", "P", "Q" };
            for (int k = 9; k <= 16; k++)
            {
                if (k != 10 && k != 12 && k != 13 && k != 15)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "SUM(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }


            for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
            {
                //fix lai border
                if (ik < 6 || ik == 7 || ik == 8 || ik == 10 || ik == 12 || ik == 13 || ik == 15)
                {
                    r1c1 = row.CreateCell(ik);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            //end sheet tong hop--------------------------

            //in tung bien so vao tung sheet
            #region Print bienso

            string bienso = "";
            if (dlBienSo.SelectedIndex.Equals(0))
            {

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        bienso = dt.Rows[i]["BienSo"].ToString();

                        if (!bienso.Equals(dt.Rows[i - 1]["BienSo"].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            #region Footer

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(6);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 9; k <= 16; k++)
                            {
                                if (k != 10 && k != 12 && k != 13 && k != 15)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "SUM(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }


                            for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
                            {
                                //fix lai border
                                if (ik < 6 || ik == 7 || ik == 8 || ik == 10 || ik == 12 || ik == 13 || ik == 15)
                                {
                                    r1c1 = row.CreateCell(ik);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                                sheet.AutoSizeColumn(ik);
                            }
                            #endregion

                            //---End Footer

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(bienso));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO BIỂN SỐ XE " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //bien so
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Biển số: " + bienso);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;
                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            STT++;

                        }
                    }
                    else
                    {
                        bienso = "";
                        if (!bienso.Equals(dt.Rows[i]["BienSo"].ToString()))
                        {
                            bienso = dt.Rows[i]["BienSo"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(bienso));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO BIỂN SỐ XE " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //bien so
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Biển số: " + bienso);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;
                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j <= dt.Columns.Count - 1; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j == 9 || j == 11 || j >= 13)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j <= 2 || j == 5 || j == 7 || j == 8 || j == 10)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(6);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 9; k <= 16; k++)
                        {
                            if (k != 10 && k != 12 && k != 13 && k != 15)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "SUM(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }


                        for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
                        {
                            //fix lai border
                            if (ik < 6 || ik == 7 || ik == 8 || ik == 10 || ik == 12 || ik == 13 || ik == 15)
                            {
                                r1c1 = row.CreateCell(ik);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }
            }
            #endregion

            //In doanh thu tong hop bien so
            if (dlBienSo.SelectedIndex.Equals(0))
            {

                sheet = workbook.CreateSheet("Tổng hợp doanh thu xe");


                getDate();
                p = new SqlParameter[2];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;

                dt = ex.GetData("sp_BCDT_BSX_TongDoanhThuXe", p);

                //Start header-----------------------------------------------------------------------
                #region Header

                rowIndex = 0;

                //cong ty
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO BIỂN SỐ XE " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(2, 2, 0, 12);
                sheet.AddMergedRegion(cra);

                rowIndex++;

                //freeze panes
                sheet.CreateFreezePane(0, 6);
                #endregion


                #region Header1-2

                //header2
                rowIndex++;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                string[] header2 = { "Biển số", "Số chuyến", "Thành tiền CVC" };

                cell = row.CreateCell(0);
                cell.SetCellValue("STT");
                cell.CellStyle = hs4;
                cell.Row.Height = 400;


                //tao tieu de cot
                for (int j = 0; j < header2.Length; j++)
                {
                    cell = row.CreateCell(j + 1);
                    cell.SetCellValue(header2[j].ToString());
                    cell.CellStyle = hs4;
                    cell.Row.Height = 500;
                }
                #endregion
                //End header-----------------------------------------------------------------------
                //ghi du lieu tung dong
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue((i + 1).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j == 2 || j == 3)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            if (j == 1)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                }

                #region Footer

                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                cell = row.CreateCell(1);
                cell.SetCellValue("Tổng cộng");
                cell.Row.Height = 500;
                cell.CellStyle = hs4;

                string[] footer2 = { "C", "D" };
                for (int k = 2; k <= 3; k++)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "SUM(" + footer2[k - 2] + "7:" + footer2[k - 2] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }

                r1c1 = row.CreateCell(0);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;

                for (int ik = 0; ik <= dt.Columns.Count; ik++)
                {

                    sheet.AutoSizeColumn(ik);
                }
                #endregion
            }

            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlBienSo.SelectedIndex > 0 ? dlBienSo.SelectedItem.Text : "";
                string saveAsFileName = "BCDT-BienSoXe-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                int index = dlBienSo.SelectedIndex > 0 ? 1 : 2;


                getDate();
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@BienSo", ex.GetGuid(dlBienSo.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@Index", index);
                p[3].SqlDbType = SqlDbType.Int;

                dtPrint = ex.GetData("sp_BCDT_BSX_Print", p);
                PrintNPOI(dtPrint);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");

            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBienSo.Items.Clear();
        dlBienSo.DataSource = ex.GetData("sp_BCDT_BSX_LoadBienSoXe", p);
        dlBienSo.DataBind();
        dlBienSo.Items.Insert(0, new ListItem("--Chọn biển số--", ""));
        dlBienSo.SelectedIndex = 0;

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
}